package com.apestech.framework.metadata;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.apestech.framework.db.SqlResultSet;
import com.apestech.framework.db.callback.QueryCallBackHandler;
import com.apestech.framework.json.JSON;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.xml.table.modul.Tables;

public class MetaData {

	public class TableInfo{
		private String tableName;
		private String schema;

		public TableInfo(String tableName, String schema){
			this.tableName=tableName;
			this.schema=schema;
		}

		public String getTableName() {
			return tableName;
		}

		public String getSchema() {
			return schema;
		}
	}

	private DatabaseMetaData dbmd;
	private String catalog="";
	private String schema="";
	private Connection conn;
	private OracleDBMD odbmd=null;

	public MetaData(Connection conn, String schema) throws Exception {
		this.schema=schema;
		this.conn = conn;
		dbmd=this.conn.getMetaData();
		catalog=this.conn.getCatalog();
		odbmd=new OracleDBMD(conn);
	}

	/**
	 * 表所在的类别名称;""表示获取没有类别的列,null表示获取所有类别的列。
	 * @return
	 */
	public String getCatalog(){
		if(!"".equals(catalog)){
			return catalog;
		} else {
			return null;
		}
	}

	/**
	 * 表所在的模式名称(oracle中对应于Tablespace);""表示获取没有模式的列,null标识获取所有模式的列; 可包含单字符通配符("_"),或多字符通配符("%");
	 * @return
	 */
	public String getSchema(){
		if(!"".equals(schema)){
			return schema;
		} else {
			return "%";
		}
	}

	public Connection getConnection(){
		return this.conn;
	}

	public boolean tableExists(String tableName, boolean local) throws Exception {
		if(local) return tableExists(tableName, schema);
		else return tableExists(tableName, getTableSchema(getTableList(tableName)));
	}

	public boolean tableExists(String tableName) throws Exception {
		return tableExists(tableName, false);
	}

	public boolean tableExists(String tableName, String schema) throws Exception {
		final boolean[] result = {false};
		odbmd.getTables(schema, tableName, new QueryCallBackHandler() {
			@Override
			public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
				JSONArray qlist=rs.toJSON();
				int len=qlist.size();
				if(len>0) result[0] =true;
			}
		});
		return result[0];
	}

	public boolean functionExists(String id) throws SQLException {
		ResultSet rs=dbmd.getFunctions(getCatalog(), "%", id);
		boolean result=false;
		if(rs.next()){
			result=true;
		}
		rs.close();
		return result;
	}

	public boolean procedureExists(String id) throws SQLException {
		ResultSet rs=dbmd.getProcedures(getCatalog(), "%", id);
		boolean result=false;
		if(rs.next()){
			result=true;
		}
		rs.close();
		return result;
	}

	/**
	 * 获取表基础信息：表名ID，中文表名，主键名，非空字段，默认值字段，字段列表
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public JSONObject getTable(String tableName) throws Exception{
		JSONObject table=getTableInfo(tableName);
		table.putAll(getTableKey(tableName));
		table.putAll(getTableColumn(tableName));
		return table;
	}
	
	/**
	 * 获取表的基础信息，表名ID，中文表名
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public JSONObject getTableInfo(String tableName) throws Exception{
		final JSONArray[] list = {null};
		odbmd.getTables(getTableSchema(getTableList(tableName)), tableName, new QueryCallBackHandler() {
			@Override
			public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
				list[0] =rs.toJSON("TABLE_NAME;REMARKS");
			}
		});
		if(list[0].size()>0){
			return list[0].get(0);
		} else {
			return null;
		}
	}

	public JSONObject getAllTable() throws Exception {
		JSONObject m=new JSONObject();
		odbmd.getTables(getSchema(), null, new QueryCallBackHandler() {
			@Override
			public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
				while(rs.next()){
					String tabname=rs.getString("TABLE_NAME");
					String remarks=rs.getString("REMARKS");
					if(remarks!=null){
						m.put(remarks, tabname);
					}
				}
			}
		});
		if(m.size()>0){
			return m;
		} else {
			return null;
		}
	}

	public ArrayList<String> getAllTableName() throws Exception {
		ArrayList<String> m=new ArrayList<String>();
		odbmd.getTables(getSchema(), null, new QueryCallBackHandler() {
			@Override
			public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
				while(rs.next()){
					String tabname=rs.getString("TABLE_NAME");
					m.add(tabname);
				}
			}
		});
		if(m.size()>0){
			return m;
		} else {
			return null;
		}
	}

	public void qryTable(TableInfoCallback tableInfoCallback) throws Exception {
		odbmd.getTables(getSchema(), null, new QueryCallBackHandler() {
			@Override
			public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
				while(rs.next()){
					tableInfoCallback.getTable(rs);
				}
			}
		});
	}
	
	public void getTable(QueryCallBackHandler queryCallBack) throws Exception{
		odbmd.getTables(getSchema(), null, queryCallBack);
	}

	public void getColumns(QueryCallBackHandler queryCallBack) throws Exception{
		odbmd.getColumns(getSchema(), null, null, queryCallBack);
	}
	
	public void getColumn(String tabName, QueryCallBackHandler queryCallBack) throws Exception{
		int pos=tabName.indexOf(".");
		if(pos>=0){
			String s=tabName.substring(0, pos);
			String tab=tabName.substring(pos+1, tabName.length());
			odbmd.getColumns(s, tab, null, queryCallBack);
		} else {
			String c=getCatalog();
			String s=getTableSchema(getTableList(tabName));
			odbmd.getColumns(s, tabName, null, queryCallBack);
		}
	}

	public SqlResultSet getProcedure() throws SQLException{
		SqlResultSet rs=new SqlResultSet(dbmd.getProcedureColumns(getCatalog(), getSchema(), null, null));
		return rs;
	}
	
	public SqlResultSet getProcedure(String procName) throws SQLException{
		SqlResultSet rs=new SqlResultSet(dbmd.getProcedureColumns(getCatalog(), getSchema(), procName, null));
		return rs;
	}
	
	/**
	 * 获取表的主键信息
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public JSONObject getTableKey(String tableName) throws Exception{
		JSONObject map=new JSONObject();
		odbmd.getPrimaryKeys(getTableSchema(getTableList(tableName)), tableName, new QueryCallBackHandler() {
			@Override
			public void getData(SqlResultSet key, JSONArray rslist) throws Exception {
				String COLUMN_NAME="";
				String PK_NAME="";
				while(key.next()){
					if(!"".equals(COLUMN_NAME)){
						COLUMN_NAME=COLUMN_NAME+";";
					}
					COLUMN_NAME=COLUMN_NAME+key.getString("COLUMN_NAME");
					if("".equals(PK_NAME)){
						PK_NAME=key.getString("PK_NAME");
					}
				}
				map.put("PK_NAME", PK_NAME);
				map.put("PK_COLUMN", COLUMN_NAME);
			}
		});
		return map;
	}

	public JSONObject getImportedKey(String tableName) throws Exception{
		JSONObject map=new JSONObject();
		odbmd.getImportedKey(getTableSchema(getTableList(tableName)), tableName, new QueryCallBackHandler(){
			@Override
			public void getData(SqlResultSet key, JSONArray rslist) throws Exception {
				String table="";
				while(key.next()){
					JSONObject m=new JSONObject();
					String id=key.getString("FKCOLUMN_NAME");
					String tab=key.getString("PKTABLE_NAME");
					String fkey=key.getString("PKCOLUMN_NAME");
					String fkeyName=key.getString("FK_NAME");
					m.put("PKCOLUMN_NAME", fkey);
					m.put("PKTABLE_NAME", tab);
					m.put("FK_NAME", fkeyName);
					if(tab.equals(tableName)){
						m.put("TREE_TABLE", tab);
						m.put("TREE_ID", fkey);
						m.put("TREE_PID", id);
					} else {
						if((";"+table+";").indexOf(";"+tab+";")<0){
							String fid=getTreeInfo(tab);
							if(fid!=null){
								m.put("TREE_TABLE", tab);
								m.put("TREE_ID", fkey);
								m.put("TREE_PID", fid);
							}
							table=table+";"+tab;
						}
					}
					map.put(id, m);
				}
			}
		});
		return map;
	}

	private String getTreeInfo(String tableName) throws Exception {
		final String[] id = {null};
		odbmd.getImportedKey(getTableSchema(getTableList(tableName)), tableName, new QueryCallBackHandler() {
			@Override
			public void getData(SqlResultSet key, JSONArray rslist) throws Exception {
				while(key.next()){
					String tab=key.getString("PKTABLE_NAME");
					if(tab.equals(tableName)){
						if(id[0] ==null){
							id[0] =key.getString("FKCOLUMN_NAME");
						} else {
							id[0] =null;
							break;
						}
					}
				}
			}
		});
		return id[0];
	}
	
	/**
	 * 获取表字段列表
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public JSONObject getTableColumn(String tableName) throws Exception{
		JSONObject map=new JSONObject();
		odbmd.getColumns(getTableSchema(getTableList(tableName)), tableName, null, new QueryCallBackHandler() {
			@Override
			public void getData(SqlResultSet rscol, JSONArray rslist) throws Exception {
				JSONArray list=new JSONArray();
				String COLUMN_NAME="";
				while(rscol.next()){
					JSONObject map=new JSONObject();
					String NAME=rscol.getString("COLUMN_NAME");
					map.put("COLUMN_NAME", NAME);
					map.put("REMARKS", rscol.getObject("REMARKS"));
					String TYPE_NAME=rscol.getString("TYPE_NAME");
					String DEF=rscol.getString("COLUMN_DEF");
					if(DEF!=null){
						DEF=DEF.trim();
						map.put("COLUMN_DEF", DEF);
					}
					String NULL=rscol.getString("NULLABLE");
					if("0".equals(NULL)){
						map.put("NOTNULL", "1");
					}
					if(!"".equals(COLUMN_NAME)){
						COLUMN_NAME=COLUMN_NAME+";";
					}
					COLUMN_NAME=COLUMN_NAME+NAME;
					map.put("TYPE_NAME", TYPE_NAME);
					if("VARCHAR2".equals(TYPE_NAME)){
						map.put("LENGTH", rscol.getObject("CHAR_OCTET_LENGTH"));
						map.put("CLASSNAME", "java.lang.String");
					} else if("NUMBER".equals(TYPE_NAME)){
						map.put("LENGTH", rscol.getObject("COLUMN_SIZE"));
						map.put("SIZE", rscol.getObject("DECIMAL_DIGITS"));
						map.put("CLASSNAME", "java.math.BigDecimal");
					} else if("DATE".equals(TYPE_NAME)){
						map.put("CLASSNAME", "java.sql.Timestamp");
					}
					list.add(map);
				}
				map.put("COLUMN_NAME", COLUMN_NAME);
				map.put("COLUMNS", list);
			}
		});
		return map;
	}
	
	public JSONArray getProcedureColumns(String procName) throws SQLException{
		ResultSet proc= dbmd.getProcedureColumns(getCatalog(), getSchema(), procName, null);
		JSONArray list=new JSONArray();
		while(proc.next()){
			JSONObject map=new JSONObject();
			map.put("COLUMN_NAME", proc.getObject("COLUMN_NAME"));
			String TYPE_NAME=proc.getString("TYPE_NAME");
			String COLUMN_TYPE=proc.getString("COLUMN_TYPE");
			if("VARCHAR2".equals(TYPE_NAME)){
				map.put("CLASSNAME", "java.lang.String");
			} else if("NUMBER".equals(TYPE_NAME)){
				map.put("CLASSNAME", "java.math.BigDecimal");
			} else if("DATE".equals(TYPE_NAME)){
				map.put("CLASSNAME", "java.sql.Timestamp");
			}
			if("1".equals(COLUMN_TYPE)){
				map.put("TYPE", "in");
			} else if("2".equals(COLUMN_TYPE)){
				map.put("TYPE", "inout");
			} else if("4".equals(COLUMN_TYPE)){
				map.put("TYPE", "out");
			}
			map.put("COLUMN_TYPE", COLUMN_TYPE);
			map.put("TYPE_NAME", TYPE_NAME);
			list.add(map);
		}
		proc.close();
		proc=null;
		return list;
	}

	public ArrayList<TableInfo> getTableList(String tableName) throws Exception {
		String s=getSchema();
		int pos=tableName.indexOf(".");
		if(pos>=0){
			s=tableName.substring(0, pos);
			tableName=tableName.substring(pos+1);  //, tableName.length()
		}
		ArrayList<TableInfo> info=new ArrayList<>();
		odbmd.getTables(s, tableName, new QueryCallBackHandler() {
			@Override
			public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
				while(rs.next()){
					info.add(new TableInfo(rs.getString("TABLE_NAME"), rs.getString("TABLE_SCHEM")));
				}
			}
		});
		if(info.size()==0){
			odbmd.getTables(null, tableName, new QueryCallBackHandler() {
				@Override
				public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
					while(rs.next()){
						info.add(new TableInfo(rs.getString("TABLE_NAME"), rs.getString("TABLE_SCHEM")));
					}
				}
			});
		}
		return info;
	}

	public JSONArray getExportedKeys(String tableName) throws Exception {
		SqlResultSet rs;
		int pos=tableName.indexOf(".");
		if(pos>=0){
			String s=tableName.substring(0, pos);
			String tab=tableName.substring(pos+1, tableName.length());

			rs=new SqlResultSet(dbmd.getExportedKeys(getCatalog(), s, tab));
		} else {
			ResultSet trs=dbmd.getExportedKeys(getCatalog(), getTableSchema(getTableList(tableName)), tableName);
			rs=new SqlResultSet(trs);
		}
		JSONArray list=rs.toJSON("FKTABLE_NAME;FKCOLUMN_NAME;PKTABLE_NAME;PKCOLUMN_NAME");
		rs.close();
		return list;
	}

	public JSONObject getIndexInfo(String tableName) throws Exception {
		JSONObject result=new JSONObject();
		odbmd.getIndexInfo(getTableSchema(getTableList(tableName)), tableName, new QueryCallBackHandler(){
			@Override
			public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
				while(rs.next()){
					String INDEX_NAME=rs.getString("INDEX_NAME");
					String COLUMN_NAME=rs.getString("COLUMN_NAME");
					if(INDEX_NAME!=null){
						String s=result.getString(INDEX_NAME);
						if(s==null) s=COLUMN_NAME;
						else {
							s=s+","+COLUMN_NAME;
						}
						result.put(INDEX_NAME, s);
					}
				}
			}
		});
		return result;
	}

	public Tables getTables(String tableName) throws Exception {
		Tables tables=new Tables();
		tables.setId(tableName);
		tables.addMasterTab(tableName, tableName, "1","1");
		setTables(tableName, tables);
		return tables;
	}

	private void setTables(String tableName, Tables tables) throws Exception {
		JSONArray list=getExportedKeys(tableName);
		int len=list.size();
		if(len>0){
			for(int i=0; i<len; i++){
				String tab=list.get(i).getString("FKTABLE_NAME");
				tables.addMasterTab(tab, tab, tableName,tableName);
				setTables(tab, tables);
			}
		}
	}

	private String getTableSchema(ArrayList<TableInfo> tablist) throws Exception {
		int len=tablist.size();
		if(len==0) return null; //throw new Exception("当前数据库里没有该表！");
		return tablist.get(0).getSchema();
	}

	/**
	 * 获取表字段列表
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public String getColumnRemark(String colname, String tableName) throws Exception{
		final String[] remark = {null};
		odbmd.getColumns(getTableSchema(getTableList(tableName)), tableName, colname, new QueryCallBackHandler() {
			@Override
			public void getData(SqlResultSet rscol, JSONArray rslist) throws Exception {
				if(rscol.next()){
					remark[0] =rscol.getString("REMARKS");
				}
			}
		});
		return remark[0];
	}

}
